//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - SaaS Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================


namespace Shp.Runtime.OfflineSitka
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Collections.ObjectModel;
    using System.Globalization;
    using System.Threading;

    public class SitkaToSqlQuery
    {
        private const string WhereStatment = " WHERE ";
        private const string SelectStatment = " SELECT ";

        private Dictionary<string, string> operationTranslate;
        private string sitkaQuery;
        private StringBuilder querySql;
        private string propertyFilter;
        private string operand;

        public SitkaToSqlQuery(string sitkaQuery)
        {
            this.sitkaQuery = sitkaQuery;
            InitializeOperationTranslate();
        }

        public Collection<Token> TokensSitkaQuery { get; private set; }

        public static string SqlQuery(string sitkaQuery, string containerId, string entityId)
        {
            SitkaToSqlQuery sitkaToSql = new SitkaToSqlQuery(sitkaQuery);
            return sitkaToSql.SqlQuery(containerId, entityId);
        }

        public string SqlQuery(string containerId, string entityId)
        {
            ProcessSitkaQuery();

            StringBuilder sqlQuery = new StringBuilder();
            sqlQuery.Append("SELECT id, entity, version, kind FROM DATA WHERE containerId='" + containerId + "'");

            if (!string.IsNullOrEmpty(entityId))
            {
                sqlQuery.Append(" AND Id='" + entityId + "'");
            }

            string where = TranslateSitkaTokensToSql().Trim();
            if (!string.IsNullOrEmpty(where))
            {
                sqlQuery.Append(" AND ");
                sqlQuery.Append(where);
            }

            return sqlQuery.ToString();
        }

        private void InitializeOperationTranslate()
        {
            operationTranslate = new Dictionary<string, string>();
            operationTranslate.Add("&&", "AND");
            operationTranslate.Add("||", "OR");
            operationTranslate.Add("==", "=");
            operationTranslate.Add("<", "<");
            operationTranslate.Add(">", ">");
            operationTranslate.Add(">=", ">=");
            operationTranslate.Add("<=", "<=");
            operationTranslate.Add("!=", "<>");
        }

        private string GetWhere()
        {
            string query = sitkaQuery.ToUpperInvariant();
            string where = string.Empty;
            int indexWhere = query.IndexOf(WhereStatment, StringComparison.OrdinalIgnoreCase);
            if (indexWhere != -1)
            {
                where = query.Substring(indexWhere + WhereStatment.Length);
                int indexSelect = where.IndexOf(SelectStatment, StringComparison.OrdinalIgnoreCase);
                int lastIndexSelect = indexSelect;
                while (indexSelect != -1)
                {
                    lastIndexSelect = indexSelect;
                    indexSelect = where.IndexOf(SelectStatment, lastIndexSelect + SelectStatment.Length, StringComparison.OrdinalIgnoreCase);
                }

                where = sitkaQuery.Substring(indexWhere + WhereStatment.Length);
                where = where.Remove(lastIndexSelect);
            }

            return where;
        }

        private void ProcessSitkaQuery()
        {
            TokensSitkaQuery = new Collection<Token>();
            TokenProvider tockenProvider = new TokenProvider(GetWhere());
            Token token = tockenProvider.Next;
            while (token != null)
            {
                TokensSitkaQuery.Add(token);
                token = tockenProvider.Next;
            }
        }        

        private string TranslateSitkaTokensToSql()
        {
            querySql = new StringBuilder();
            foreach (Token token in TokensSitkaQuery)
            {
                switch (token.TokenType)
                {
                    case TokenType.IntrinsicProperty:
                        TranslateIntrinsicProperty(token);
                        break;
                    case TokenType.CustomProperty:
                        TranslateCustomProperty(token);                        
                        break;
                    case TokenType.Operator:
                        TranslateOperator(token);
                        break;
                    case TokenType.Symbol:
                        TranslateSymbol(token);
                        break;
                    case TokenType.StringValue:
                        TranslateString(token);           
                        break;
                    case TokenType.BoolValue:
                        TranslateBool(token);           
                        break;
                    case TokenType.DateValue:
                        TranslateDate(token);
                        break;                                        
                }
            }
            return querySql.ToString();
        }

        private void TranslateSymbol(Token token)
        {
            querySql.Append(" " + token.Value + " ");
        }

        private void TranslateOperator(Token token)
        {
            if (String.IsNullOrEmpty(propertyFilter))
            {
                querySql.Append(" " + operationTranslate[token.Value] + " ");
            }
            else
            {
                operand = " " + operationTranslate[token.Value] + " ";
            }
        }        

        private void TranslateString(Token token)
        {
            if (String.IsNullOrEmpty(propertyFilter))
            {
                querySql.Append(" '" + token.Value + "' ");
            }
            else
            {
                querySql.AppendFormat(CultureInfo.InvariantCulture, propertyFilter, "NVARCHAR(MAX)", operand, " '" + token.Value + "' ");
                propertyFilter = string.Empty;
            }
        }

        private void TranslateBool(Token token)
        {
            if (String.IsNullOrEmpty(propertyFilter))
            {
                querySql.Append(" '" + token.Value + "' ");
            }
            else
            {
                querySql.AppendFormat(CultureInfo.InvariantCulture, propertyFilter, "NVARCHAR(MAX)", operand, " '" + token.Value + "' ");
                propertyFilter = string.Empty;
            }
        }

        private void TranslateDate(Token token)
        {
            DateTime date;
            DateTime.TryParse(token.Value, Thread.CurrentThread.CurrentCulture, DateTimeStyles.AssumeLocal, out date);
            long utcTime = date.ToFileTimeUtc();
            if (String.IsNullOrEmpty(propertyFilter))
            {
                querySql.Append(" '" + utcTime.ToString(CultureInfo.InvariantCulture) + "' ");
            }
            else
            {
                querySql.AppendFormat(CultureInfo.InvariantCulture, propertyFilter, "NVARCHAR(MAX)", operand, " '" + utcTime.ToString(CultureInfo.InvariantCulture) + "' ");
                propertyFilter = string.Empty;
            }
        }

        private void TranslateIntrinsicProperty(Token token)
        {
            querySql.Append(" " + token.Value + " ");
        }

        private void TranslateCustomProperty(Token token)
        {
            propertyFilter = @" entity.value('(/entity/field[@name=""" + token.Value + @"""])[1]','{0}'){1}{2} ";
        }
    }
}
